from datetime import datetime, timedelta
import sqlite3

import concurrent
from concurrent.futures import ThreadPoolExecutor
import traceback

from common.enum.operation_log_type_enum import LogType
from constant import Constants
from dto.stock_risk_assessment import RiskAssessmentDTO
from dto.stock_risk_assessment_item import AntRiskAssessmentItemDto
from dto.strategy_calculate_result import StrategyCalculateResult
from service.das.operation_log import OperationLogDas
from service.das.stock_assessment_das import (
    AntRiskAssessmentModel,
    RiskAssessmentService,
)
from service.das.stock_assessment_item_das import RiskAssessmentItemService
from service.das.stock_base_info_das import StockInfoService
from service.das.stock_day_das import StockDateDayDas
from service.das.stock_monitor_das import StockDas
from service.stock_analyse_service import StockAnalyseService
from service.stock_service import StockMainService
from service.strategy_engine_service import StrategyService
from .das.database_pool import DatabasePool
from .das.tushare_pool import getDayStock, getRealTime, query_all_stock_info


# 初始化监控系统表结构
class DBInit:
    def checkAndInitDB(self):
        table_name = "ant_monitor_stock"
        # 检查表是否存在
        if not self.check_table_exists(table_name):
            AntLogger.info(f"表 '{table_name}' 不存在，正在创建...")
            self.init_db()
        else:
            AntLogger.info(f"表 '{table_name}' 已存在。")

    def check_table_exists(self, table_name):
        """检查指定表是否存在"""
        db_pool = DatabasePool(max_connections=1)
        cursor = db_pool.connect().cursor()
        cursor.execute(
            f"""
            SELECT name FROM sqlite_master WHERE type='table' AND name='{table_name}';
        """
        )
        table_exists = cursor.fetchone() is not None
        cursor.close()
        return table_exists

    # 初始化所有的SQL

    def init_db(self):
        db_pool = DatabasePool(max_connections=1)
        conn = db_pool.connect()
        cursor = conn.cursor()
        try:
            # 读取 SQL 文件
            sql_file = "./resources/base.sql"
            with open(sql_file, "r", encoding="utf-8") as f:
                sql_script = f.read()

            # 分割 SQL 语句并执行
            for statement in sql_script.split(";"):
                statement = statement.strip()
                if statement:  # 确保不是空语句
                    cursor.execute(statement)

            # 提交事务
            conn.commit()
            AntLogger.info("所有 SQL 语句执行成功！")

        except sqlite3.Error as e:
            AntLogger.info(f"执行 SQL 时发生错误: {e}")

        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()


class RefreshStockInit:
    def refreshAllStockInfo(self):

        log_das = OperationLogDas()

        # 假设我们传入时间差为 8 小时和小时限制为 15 点
        can_perform = log_das.should_perform_action(
            LogType.GLOBAL_COMPUTATION, time_diff_limit=8, hour_limit=15
        )

        if can_perform:
            stockDas = StockDas()
            stockList = stockDas.get_stocks_code()

            self.process_stock_base_info()

            with concurrent.futures.ThreadPoolExecutor(max_workers=1) as executor:
                futures = {
                    executor.submit(self.process_stock_flow, stock_code): stock_code
                    for stock_code in stockList
                }

                for future in concurrent.futures.as_completed(futures):
                    stock_code = futures[future]
                    try:
                        future.result()
                    except Exception as e:
                        AntLogger.info(f"Error in thread for stock {stock_code}: {e}")
                        traceback.print_exc()

            log_das.add_log(
                execution_type=LogType.GLOBAL_COMPUTATION.value,
                execution_content="全局数据计算完成。",
                execution_module="STOCK_INFO",
            )
        else:
            log_das.add_log(
                execution_type=LogType.COMMON_OPERATION.value,
                execution_content="不符合刷新条件,跳过全局统计",
                execution_module="STOCK_INFO",
            )

    def process_stock_base_info():

        # 遍历数据并保存
        stock_data_list = query_all_stock_info()
        stock_service = StockInfoService()

        if not stock_data_list:
            AntLogger.info("未查询到股票数据，退出数据处理流程。")
            return

        # 清空数据库中已有的数据
        try:
            all_stocks = stock_service.get_all_stock_info()
            for stock in all_stocks:
                stock.delete_instance()
            AntLogger.info("已清空数据库中原有的股票数据。")
        except Exception as e:
            AntLogger.info(f"清空数据库数据时出错: {e}")

        # 插入新的股票数据
        for stock_data in stock_data_list:
            try:
                # 插入数据
                stock_service.add_stock_info(stock_data)
            except Exception as e:
                AntLogger.info(f"插入股票数据时出错: {e}")
        AntLogger.info("所有新股票数据已插入到数据库中。")

    def process_stock_flow(self, stock_code):

        AntLogger.info(f"开始执行每日数据分析:{stock_code}")

        # S1 : 刷新每日股票信息
        self.process_stock_day(stock_code)
        self.process_stock_kline(stock_code)

        # S2 : 计算股票风险等级
        final_result = self.process_stock_strategy(stock_code)

        # S3 : 保存风险等级
        self.process_risk_assessment(final_result)

    def process_stock_day(self, stock_code):
        """
        查询最后交易日期并调用 getDayStock 方法处理
        """
        try:

            AntLogger.info("开始处理单个配置的数据")

            # S1 : 先查询最后一个交易日的数据
            stockDay = StockDateDayDas()
            last_trade_date = stockDay.get_last_trade_date(stock_code)
            current_date = datetime.now()

            # S2 :如果找到最后交易日期，从最后交易日期后开始查询
            # S2-1 : 如果未找到交易日期，则查询最近 180 天的数据
            if last_trade_date:
                last_trade_date_dt = datetime.strptime(str(last_trade_date), "%Y%m%d")
                if last_trade_date_dt < current_date:
                    start_time = (last_trade_date_dt + timedelta(days=1)).strftime(
                        "%Y%m%d"
                    )
                else:
                    AntLogger.info(f"当前股票 {stock_code}, 无需刷新.")
                    return
            else:
                AntLogger.info(f"No trade data found for {stock_code}, querying last 180 days.")
                start_time = (datetime.today() - timedelta(days=560)).strftime("%Y%m%d")

            end_time = datetime.today().strftime("%Y%m%d")

            # S3 : 调用 getDayStock 查询数据
            stock_data = getDayStock(stock_code, start_time, end_time)
            if not stock_data.empty:
                stockDay.add_stock(stock_data)
                AntLogger.info
                    f"Data for {stock_code} from {start_time} to {end_time} added successfully."
                )
            else:
                AntLogger.info(f"No new data found for {stock_code} in the specified range.")

            # S4 ：更新 Stock Monitor 每日数据
            stock_das = StockDas()
            stockInfo = stock_das.get_stocks_byCode(stock_code)
            stockService = StockMainService()
            stockInfo = stockService.buildStockMonitorInfo(stockInfo)
            stock_das.update_stock_bycode(stockInfo)
        except Exception as e:
            AntLogger.info(f"Error processing stock {stock_code}: {e}")

    def process_stock_kline(self, stock_code):
        stockAnalyseService = StockAnalyseService()
        stockAnalyseService.calculate_and_store_kline(stock_code)

    def process_stock_strategy(self, stock_code) -> StrategyCalculateResult:
        strategyEngine = StrategyService()
        return strategyEngine.do_strategy_flow(stock_code)

    def process_risk_assessment(self, strategy_result: StrategyCalculateResult):
        """
        将 StrategyCalculateResult 的信息分别保存到 RiskAssessmentItemService 和 RiskAssessmentService。

        :param strategy_result: StrategyCalculateResult 对象
        :param risk_item_service: RiskAssessmentItemService 实例
        :param risk_service: RiskAssessmentService 实例
        """
        risk_service = RiskAssessmentService()
        risk_item_service = RiskAssessmentItemService()

        # 删除原有的父记录
        risk_service.delete_risk_assessment_by_code(
            strategy_result.stock_info.stock_code
        )
        risk_item_service.delete_risk_assessment_item_by_code(
            strategy_result.stock_info.stock_code
        )

        # 保存每个 RiskAssessment 到 RiskAssessmentItemService
        for risk in strategy_result.risk_assessments:
            item_dto = AntRiskAssessmentItemDto(
                stock_code=strategy_result.stock_info.stock_code,
                create_time=datetime.now(),
                description=risk.description,
                risk_type=risk.config.strategy_type,
                risk_level=strategy_result.risk_level,
                stock_tag_code="",  # 可以根据需要定义匹配标签
                strategy_code=risk.config.strategy_code,
                strategy_name=risk.config.strategy_name,
                strategy_group=risk.config.strategy_group.code,
                strategy_type=risk.config.strategy_type.code,
                analysis_day=risk.config.analysis_day,
                strategy_level=risk.config.strategy_level or 0,
                node_point=risk.node_point or 0,
            )
            risk_item_service.add_risk_assessment_item(item_dto)

        # 组合 StrategyCalculateResult 保存到 RiskAssessmentService
        parent_dto = RiskAssessmentDTO(
            stock_code=strategy_result.stock_info.stock_code,
            description="综合评分记录",
            risk_type="综合风险评估",
            risk_level=strategy_result.risk_level,  # 综合风险等级
            stock_hold_ratio=min(
                strategy_result.risk_level * 10, 100
            ),  # 根据风险等级计算建议持股比例
            future_action=(
                "观察"
                if strategy_result.risk_level > 5
                else "买入" if strategy_result.focus_level > 5 else "持有"
            ),  # 决策条件更细化
            stock_tag_code="综合分析",
            stock_tag_desc=(
                f"总得分: {strategy_result.total_score}, "
                f"风险等级: {strategy_result.risk_level}, "
                f"关注等级: {strategy_result.focus_level}"
            ),  # 增加关注等级描述
            final_point=strategy_result.total_score,
        )
        risk_service.add_risk_assessment(parent_dto)
